
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[factACIAlerts]') AND type in (N'P'))
DROP PROCEDURE [dbo].[factACIAlerts]
GO

-- =============================================
-- Author:		Kristinn Thor Sigurjonsson
-- Modified Date: 2021-06-14
-- Description:	SP with option of Incremental load for actionable Insight fact table fACIAlertDetails
-- =============================================

CREATE PROCEDURE [dbo].[factACIAlerts] 

	@AlertDetailRowID bigint,
	@AlertDetailNewRowID bigint 
	 	
AS
BEGIN
SET NOCOUNT ON;

--CHECK if All needed ACI staging tables exist
IF (Select count(*) from sys.tables where SCHEMA_NAME(schema_id) LIKE 'dbo' 
AND name IN ('stg$ACI Alert Detail','stg$ACI Alert', 'stg$ACI Signal', 'stg$ACI Notification', 'stg$ACI User Group Member'))
= 5 

WITH

/*
Get the list of companies used
*/
tCompanies
AS
(SELECT
		dCOM.[SK_Company] as [Company]
	   ,dCOM.[CompanyPrefix]
	FROM [DW].[dCompany] dCOM
	WHERE dCOM.[SK_Company] <> -1),
/*
Set up dimensions and support tables used with the main data process
*/
tKPICode 
AS
(
Select 
dKPI.[SK_KPICode]
,dKPI.Code
,tCOM.CompanyPrefix
FROM [DW].[dKPICode] dKPI
LEFT OUTER JOIN [tCompanies] tCOM
		ON dKPI.[Company] = tCOM.[Company]
)
,

tLocation
AS
(SELECT
		dLOC.[SK_location]
	   ,tCOM.[CompanyPrefix]
	   ,dLOC.[LocationCode]
	FROM DW.[dLocation] dLOC
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dLOC.[Company] = tCOM.[Company]),


		tPOSTerminal
AS
(SELECT
		dPOT.[SK_POSTerminal]
	   ,dPOT.[No]
	   ,tCOM.[CompanyPrefix]
	FROM [DW].[dPOSTerminal] dPOT
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dPOT.[Company] = tCOM.[Company]),

tStaff
AS
(SELECT
		dSTA.[SK_Staff]
	   ,dSTA.[ID]
	   ,dSTA.[SourceTable]
	   ,tCOM.[CompanyPrefix]
	FROM [DW].[dStaff] dSTA
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dSTA.[Company] = tCOM.[Company]),

tCustomer
AS
(SELECT
		dCUS.[SK_Customer]
	   ,dCUS.[CustomerNo]
	   ,tCOM.[CompanyPrefix]
	FROM [DW].[dCustomer] dCUS
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dCUS.Company = tCOM.Company)

,tVendor
AS
(SELECT
		dCOM.[CompanyPrefix]
	   ,dVEN.SK_Vendor
	   ,dVEN.VendorNo
	FROM [DW].[dVendor] dVEN
	LEFT JOIN [DW].[dCompany] dCOM
		ON dCOM.[SK_Company] = dVEN.[Company])

,tAlertDetail AS (
SELECT   CompanyPrefix
      ,tALD.[bigint_timestamp] AS RowID
      ,tALD.[Entry No_]
	  ,tALD.[Alert Entry No_]
	  ,tALD.[Signal Entry No_]
      ,CAST(tALD.[Signal Date] AS DATE) AS [Signal Date]
      ,CAST(tALD.[Signal Time] AS Time) AS [Signal Time]
      ,tALD.[Deleted]
	  ,tALD.[KPI Code] 
	  ,tALD.[Alert Value Decimal]
      ,tALD.[Alert Value Boolean]
      ,NULLIF(tALD.[Alert Value Date], '1753-01-01 00:00:00.000') AS [Alert Value Date]
	  ,NULLIF(tALD.[Alert Value Time], '1753-01-01 00:00:00.000') AS [Alert Value Time]
      ,tALD.[Alert Value Duration]
	  ,tALD.[Calc_ Rule Code]

  FROM [dbo].[stg$ACI Alert Detail] tALD)

,tALLAlertDetail AS (
SELECT tCOM.Company  
      ,tALD.RowID
      ,tALD.[Entry No_]
	  ,tALD.[Alert Entry No_]
      ,tALD.[Signal Date]
      ,tALD.[Signal Time]
      ,tALD.[Deleted]
	  ,tALE.Severity AS AlertSeverity
	  ,CASE tALE.Severity
	  WHEN 0 THEN 'Low'
	  WHEN 1 THEN 'Medium'
	  WHEN 2 THEN 'High'
	  ELSE 'No Alert severity'
	  END AS AlertSeverityLabel
	  ,tALE.Code 
	  ,tANO.[Severity] NotificationSeverity
	  ,CASE tANO.[Severity]
	  WHEN 0 THEN 'Low'
	  WHEN 1 THEN 'Medium'
	  WHEN 2 THEN 'High'
	  ELSE 'No Notification severity'
	  END AS NotificationSeverityLabel
	  ,tANO.Status AS NotificationStatus
	  ,CASE tANO.Status
	  WHEN 0 THEN 'Open'
	  WHEN 1 THEN 'Review'
	  WHEN 2 THEN 'Closed'
	  WHEN 3 THEN 'Closed (Resolved)'
	  ELSE 'No Status Label'
	  END AS NotificationStatusLabel
	  ,tALE.[Impact Score]
	  ,COALESCE(tKPI.SK_KPICode , -1) AS SK_KPICode
	  ,tSIG.[Item Category Code]
	  ,tSIG.[Retail Product Code]
	  ,tSIG.[Item No_]
	  ,COALESCE(tLOC.SK_Location , -1) AS SK_Location
	  ,COALESCE(tPOT.SK_POSTerminal , -1) AS SK_POSTerminal
	  ,COALESCE(tSTA.SK_Staff , -1) AS SK_Staff
	  ,COALESCE(tVEN.SK_Vendor , -1) AS SK_Vendor
	  ,COALESCE(tCUS.SK_Customer , -1) AS SK_Customer
	  ,tALD.[Alert Value Decimal]
      ,tALD.[Alert Value Boolean]
      ,tALD.[Alert Value Date]
	  ,tALD.[Alert Value Time]
      ,tALD.[Alert Value Duration]
	  ,floor(tANO.[Time to Review] / (1000 * 60)) as [Time to Review]
	  ,floor(tANO.[Time to Close] / (1000 * 60)) as [Time to Close]
	  ,tANO.[Escalated]
	  /*
	  If Notification is assigned to a user the USER GROUP CODE is extracded from [ACI User Group Member]
	  Else the USER GROUP CODE from [ACI Notification]
	  */
	  ,CASE tANO.[Active User Group Code]
	  WHEN '' THEN tAUG.[User Group Code]
	  ELSE tANO.[Active User Group Code]
	  END AS [Active User Group Code]
	  /*
	  If Notification is NOT assigned to a user the User is marked as 'Group Notification' 
	  */
	  ,CASE tANO.[Active User ID]
	  WHEN '' THEN 'Group Notification' 
	  ELSE tANO.[Active User ID]
	  END AS [Active User ID]
	  ,tALD.[Calc_ Rule Code]
	  

  FROM tAlertDetail tALD
  LEFT JOIN [dbo].[stg$ACI Alert] tALE
  ON tALD.[Alert Entry No_] = tALE.[Entry No_]
  AND tALD.CompanyPrefix = tALE.CompanyPrefix
  LEFT JOIN [dbo].[stg$ACI Signal] tSIG
  ON tALD.[Signal Entry No_] = tSIG.[Entry No_]
  AND tALD.CompanyPrefix = tSIG.CompanyPrefix
  LEFT JOIN [dbo].[stg$ACI Notification] tANO
  ON tALD.[Alert Entry No_] = tANO.[Alert Entry No_]
  AND tALD.CompanyPrefix = tANO.CompanyPrefix
  LEFT JOIN [stg$ACI User Group Member] tAUG
  ON tANO.[Active User ID] = tAUG.[Retail User ID]
  AND tANO.CompanyPrefix = tAUG.CompanyPrefix
  LEFT JOIN tCompanies tCOM
		ON tALD.[CompanyPrefix] = tCOM.[CompanyPrefix]
	LEFT JOIN tKPICode tKPI
	ON tALD.[KPI Code] = tKPI.Code
	AND tALD.CompanyPrefix = tKPI.CompanyPrefix
	LEFT JOIN [tLocation] tLOC
		ON tSIG.[CompanyPrefix] =tLOC.[CompanyPrefix]
		AND tSIG.[Location Code] = tLOC.[LocationCode]
		LEFT JOIN [tPOSTerminal] tPOT 
		ON tSIG.[CompanyPrefix] = tPOT.[CompanyPrefix]
		AND tSIG.[POS Terminal No_] = tPOT.[No]
	LEFT JOIN [tStaff] tSTA 
		ON tSIG.[CompanyPrefix] = tSTA.[CompanyPrefix]
		AND tSIG.[Staff ID] = tSTA.[ID]
		AND tSTA.[SourceTable] = 'Staff'
	LEFT JOIN [tCustomer] tCUS 
		ON tSIG.[CompanyPrefix] = tCUS.[CompanyPrefix]
		AND tSIG.[Customer No_] = tCUS.[CustomerNo]
	LEFT JOIN [tVendor] tVEN
		ON tSIG.[CompanyPrefix] = tVEN.[CompanyPrefix]
		AND tSIG.[Vendor No_] = tVEN.VendorNo

			WHERE tALD.RowID > @AlertDetailRowID
	      AND tALD.RowID <= @AlertDetailNewRowID
	)

,tALLAlertDetailRanked AS (
Select * 
,DENSE_RANK() OVER (PARTITION BY [Entry No_] ORDER BY [RowID] DESC) AS [Rank]
FROM tALLAlertDetail
)

MERGE [DW].[fACIAlerts] AS Target USING 
	(
Select [Company]
      ,[RowID]
      ,[Entry No_]
	  ,[Alert Entry No_]
      ,[Signal Date]
      ,[Signal Time]
      ,[AlertSeverity]
	  ,[AlertSeverityLabel]
	  ,[Code]
      ,[NotificationSeverity]
	  ,[NotificationSeverityLabel]
      ,[Impact Score]
      ,[SK_KPICode]
	  ,[Item Category Code]
	  ,[Retail Product Code]
	  ,[Item No_]
      ,[SK_Location]
      ,[SK_POSTerminal]
      ,[SK_Staff]
      ,[SK_Vendor]
      ,[SK_Customer]
      ,[Alert Value Decimal]
      ,[Alert Value Boolean]
      ,[Alert Value Date]
      ,[Alert Value Time]
      ,[Alert Value Duration]
      ,[Time to Review]
	  ,[Time to Close]
	  ,[Escalated]
	  ,[Active User Group Code]
	  ,[Active User ID]
	  ,[NotificationStatus]
      ,[NotificationStatusLabel]
	  ,[Calc_ Rule Code]
	  from tALLAlertDetailRanked
WHERE [Rank] = 1
AND DELETED = 0
) AS Source

ON Target.[Company] = Source.[Company]
AND Target.[Entry No_] = Source.[Entry No_]


WHEN MATCHED
	THEN UPDATE
		SET RowID = Source.RowID
		,[Alert Entry No_] = Source.[Alert Entry No_]
		,[Signal Date] = Source.[Signal Date]
,[Signal Time] = Source.[Signal Time]
,[SK_KPICode]   = Source.[SK_KPICode]
	  ,[Item Category Code] = Source.[Item Category Code]
	  ,[Retail Product Code] = Source.[Retail Product Code]
	  ,[Item No_] = Source.[Item No_]
,[SK_Location]   = Source.[SK_Location]
,[SK_POSTerminal]   = Source.[SK_POSTerminal]
,[SK_Staff]   = Source.[SK_Staff]
,[SK_Vendor]   = Source.[SK_Vendor]
,[SK_Customer]   = Source.[SK_Customer]
		,[AlertSeverity]  = Source.[AlertSeverity]
		,[AlertSeverityLabel] = Source.[AlertSeverityLabel]
		,[Code] = Source.[Code]
		,[NotificationSeverity]  = Source.[NotificationSeverity]
		,[NotificationSeverityLabel] = Source.[NotificationSeverityLabel]
		,[Impact Score]  = Source.[Impact Score]
		,[Alert Value Decimal]   = Source.[Alert Value Decimal]
      ,[Alert Value Boolean]  = Source.[Alert Value Boolean]
      ,[Alert Value Date]  = Source.[Alert Value Date]
      ,[Alert Value Time]  = Source.[Alert Value Time]
      ,[Alert Value Duration]  = Source.[Alert Value Duration]
      ,[Time to Review]  = Source.[Time to Review]
	  ,[Time to Close] = Source.[Time to Close]
	  ,[Escalated] = Source.[Escalated]
	  ,[Active User Group Code] = Source.[Active User Group Code]
	  ,[Active User ID] = Source.[Active User ID]
	  ,[NotificationStatus] = Source.[NotificationStatus]
      ,[NotificationStatusLabel] = Source.[NotificationStatusLabel]
	  ,[Calc_ Rule Code] = Source.[Calc_ Rule Code]

	WHEN NOT MATCHED BY TARGET
	THEN INSERT
			([Company]
           ,[RowID]
           ,[Entry No_]
		   ,[Alert Entry No_]
           ,[Signal Date]
           ,[Signal Time]
           ,[AlertSeverity]
		   ,[AlertSeverityLabel]
		   ,[Code]
           ,[NotificationSeverity]
		   ,[NotificationSeverityLabel]
           ,[Impact Score]
           ,[SK_KPICode]
           ,[Item Category Code]
           ,[Retail Product Code]
           ,[Item No_]
           ,[SK_Location]
           ,[SK_POSTerminal]
           ,[SK_Staff]
           ,[SK_Vendor]
           ,[SK_Customer]
           ,[Alert Value Decimal]
           ,[Alert Value Boolean]
           ,[Alert Value Date]
           ,[Alert Value Time]
           ,[Alert Value Duration]
           ,[Time to Review]
		   ,[Time to Close]
		   ,[Escalated]
		   ,[Active User Group Code]
		   ,[Active User ID]
		   ,[NotificationStatus]
			,[NotificationStatusLabel]
			,[Calc_ Rule Code])
		   VALUES
		   (Source.[Company]
           ,Source.[RowID]
           ,Source.[Entry No_]
		   ,Source.[Alert Entry No_]
           ,Source.[Signal Date]
           ,Source.[Signal Time]
           ,Source.[AlertSeverity]
		   ,Source.[AlertSeverityLabel]
		   ,Source.[Code]
           ,Source.[NotificationSeverity]
		   ,Source.[NotificationSeverityLabel]
           ,Source.[Impact Score]
           ,Source.[SK_KPICode]
           ,Source.[Item Category Code]
           ,Source.[Retail Product Code]
           ,Source.[Item No_]
           ,Source.[SK_Location]
           ,Source.[SK_POSTerminal]
           ,Source.[SK_Staff]
           ,Source.[SK_Vendor]
           ,Source.[SK_Customer]
           ,Source.[Alert Value Decimal]
           ,Source.[Alert Value Boolean]
           ,Source.[Alert Value Date]
           ,Source.[Alert Value Time]
           ,Source.[Alert Value Duration]
           ,Source.[Time to Review]
		   ,Source.[Time to Close]
		   ,Source.[Escalated]
		   ,Source.[Active User Group Code]
		   ,Source.[Active User ID]
		   ,Source.[NotificationStatus]
           ,Source.[NotificationStatusLabel]
		   ,Source.[Calc_ Rule Code])
;
ELSE
SELECT 'RowCount' = 0
END


SELECT
	'RowCount' = @@rowcount

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[factACISignals]') AND type in (N'P'))
DROP PROCEDURE [dbo].[factACISignals]
GO
/****** Object:  StoredProcedure [dbo].[factACISignals]    Script Date: 29/04/2022 09:52:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Script for SelectTopNRows command from SSMS  ******/
-- =============================================
-- Author:		Kristinn Thor Sigurjonsson
-- Modified Date: 2021-06-29
-- Description:	SP with option of Incremental load for actionable Insight fact table fACISignals
-- =============================================

CREATE PROCEDURE [dbo].[factACISignals] 

	@ACISignalsRowID bigint,
	@ACISignalsNewRowID bigint 
	 	
AS
BEGIN
SET NOCOUNT ON;

--CHECK if All needed ACI staging tables exist
IF (Select count(*) from sys.tables where SCHEMA_NAME(schema_id) LIKE 'dbo' 
AND name IN ('stg$ACI Signal'))
= 1 

WITH
/*
Get the list of companies used
*/
tCompanies
AS
(SELECT
		dCOM.[SK_Company] as [Company]
	   ,dCOM.[CompanyPrefix]
	FROM [DW].[dCompany] dCOM
	WHERE dCOM.[SK_Company] <> -1),
/*
Set up dimensions and support tables used with the main data process
*/
tKPICode 
AS
(
Select 
dKPI.[SK_KPICode]
,dKPI.Code
,tCOM.CompanyPrefix
FROM [DW].[dKPICode] dKPI
LEFT OUTER JOIN [tCompanies] tCOM
		ON dKPI.[Company] = tCOM.[Company]
)
,

tLocation
AS
(SELECT
		dLOC.[SK_location]
	   ,tCOM.[CompanyPrefix]
	   ,dLOC.[LocationCode]
	FROM DW.[dLocation] dLOC
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dLOC.[Company] = tCOM.[Company]),


		tPOSTerminal
AS
(SELECT
		dPOT.[SK_POSTerminal]
	   ,dPOT.[No]
	   ,tCOM.[CompanyPrefix]
	FROM [DW].[dPOSTerminal] dPOT
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dPOT.[Company] = tCOM.[Company]),

tStaff
AS
(SELECT
		dSTA.[SK_Staff]
	   ,dSTA.[ID]
	   ,dSTA.[SourceTable]
	   ,tCOM.[CompanyPrefix]
	FROM [DW].[dStaff] dSTA
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dSTA.[Company] = tCOM.[Company]),

tCustomer
AS
(SELECT
		dCUS.[SK_Customer]
	   ,dCUS.[CustomerNo]
	   ,tCOM.[CompanyPrefix]
	FROM [DW].[dCustomer] dCUS
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dCUS.Company = tCOM.Company)

,tVendor
AS
(SELECT
		dCOM.[CompanyPrefix]
	   ,dVEN.SK_Vendor
	   ,dVEN.VendorNo
	FROM [DW].[dVendor] dVEN
	LEFT JOIN [DW].[dCompany] dCOM
		ON dCOM.[SK_Company] = dVEN.[Company])

,tALLSignals AS (

SELECT COALESCE(tCOM.Company , -1) AS Company
		,COALESCE(tKPI.SK_KPICode , -1) AS SK_KPICode
	  ,COALESCE(tLOC.SK_Location , -1) AS SK_Location
	  ,COALESCE(tPOT.SK_POSTerminal , -1) AS SK_POSTerminal
	  ,COALESCE(tSTA.SK_Staff , -1) AS SK_Staff
	  ,COALESCE(tVEN.SK_Vendor , -1) AS SK_Vendor
	  ,COALESCE(tCUS.SK_Customer , -1) AS SK_Customer
      ,tSIG.[bigint_timestamp]  AS RowID
	  ,tSIG.[Signal Date]
	  ,tSIG.[Signal Time]
      ,tSIG.[Entry No_]
        ,[Deleted]
      ,[Add_ Dimension 1]
      ,[Add_ Dimension 2]
      ,[Add_ Dimension 3]
      ,[Add_ Dimension 4]
      ,[Add_ Dimension 5]
      ,[KPI Value Decimal 1]
      ,[KPI Value Decimal 2]
      ,[KPI Value Decimal 3]
      ,[KPI Value Decimal 4]
      ,[KPI Value Decimal 5]
      ,[KPI Value Boolean 1]
      ,[KPI Value Boolean 2]
      ,[KPI Value Boolean 3]
      ,[KPI Value Date 1]
      ,[KPI Value Date 2]
      ,[KPI Value Time 1]
      ,[KPI Value Time 2]
      ,[KPI Value Duration 1]
      ,[KPI Value Duration 2]
   
  FROM [dbo].[stg$ACI Signal] tSIG

  LEFT JOIN tCompanies tCOM
		ON tSIG.[CompanyPrefix] = tCOM.[CompanyPrefix]
	LEFT JOIN tKPICode tKPI
	ON tSIG.[KPI Code] = tKPI.Code
	AND tSIG.CompanyPrefix = tKPI.CompanyPrefix
	LEFT JOIN [tLocation] tLOC
		ON tSIG.[CompanyPrefix] =tLOC.[CompanyPrefix]
		AND tSIG.[Location Code] = tLOC.[LocationCode]
		LEFT JOIN [tPOSTerminal] tPOT 
		ON tSIG.[CompanyPrefix] = tPOT.[CompanyPrefix]
		AND tSIG.[POS Terminal No_] = tPOT.[No]
	LEFT JOIN [tStaff] tSTA 
		ON tSIG.[CompanyPrefix] = tSTA.[CompanyPrefix]
		AND tSIG.[Staff ID] = tSTA.[ID]
		AND tSTA.[SourceTable] = 'Staff'
	LEFT JOIN [tCustomer] tCUS 
		ON tSIG.[CompanyPrefix] = tCUS.[CompanyPrefix]
		AND tSIG.[Customer No_] = tCUS.[CustomerNo]
	LEFT JOIN [tVendor] tVEN
		ON tSIG.[CompanyPrefix] = tVEN.[CompanyPrefix]
		AND tSIG.[Vendor No_] = tVEN.VendorNo
		
			WHERE tSIG.[bigint_timestamp] > @ACISignalsRowID
	      AND tSIG.[bigint_timestamp] <= @ACISignalsNewRowID
		) 

,tALLSignalsRanked AS (
Select * 
,DENSE_RANK() OVER (PARTITION BY [Entry No_] ORDER BY [RowID] DESC) AS [Rank]
FROM tALLSignals
)


MERGE [DW].[fACISignals] AS TARGET USING
(SELECT  [Company]
      ,[SK_KPICode]
      ,[SK_Location]
      ,[SK_POSTerminal]
      ,[SK_Staff]
      ,[SK_Vendor]
      ,[SK_Customer]
      ,[RowID]
	  ,[Signal Date]
	  ,[Signal Time]
      ,[Entry No_]
      ,[Deleted]
      ,[Add_ Dimension 1]
      ,[Add_ Dimension 2]
      ,[Add_ Dimension 3]
      ,[Add_ Dimension 4]
      ,[Add_ Dimension 5]
      ,[KPI Value Decimal 1]
      ,[KPI Value Decimal 2]
      ,[KPI Value Decimal 3]
      ,[KPI Value Decimal 4]
      ,[KPI Value Decimal 5]
      ,[KPI Value Boolean 1]
      ,[KPI Value Boolean 2]
      ,[KPI Value Boolean 3]
      ,[KPI Value Date 1]
      ,[KPI Value Date 2]
      ,[KPI Value Time 1]
      ,[KPI Value Time 2]
      ,[KPI Value Duration 1]
      ,[KPI Value Duration 2]
  FROM tALLSignalsRanked
  Where Deleted = 0 
AND [Rank] = 1) 
AS SOURCE
ON Target.[Company] = Source.[Company]
AND Target.[Entry No_] = Source.[Entry No_]

WHEN MATCHED
	THEN UPDATE
	SET
      [SK_KPICode] = Source.[SK_KPICode]
      ,[SK_Location] = Source.[SK_Location]
      ,[SK_POSTerminal] = Source.[SK_POSTerminal]
      ,[SK_Staff] = Source.[SK_Staff]
      ,[SK_Vendor] = Source.[SK_Vendor]
      ,[SK_Customer] = Source.[SK_Customer]
      ,[RowID] = Source.[RowID]
      ,[Signal Date] = Source.[Signal Date]
	  ,[Signal Time] = Source.[Signal Time]
	  ,[Deleted] = Source.[Deleted]
      ,[Add_ Dimension 1] = Source.[Add_ Dimension 1]
      ,[Add_ Dimension 2] = Source.[Add_ Dimension 2]
      ,[Add_ Dimension 3] = Source.[Add_ Dimension 3]
      ,[Add_ Dimension 4] = Source.[Add_ Dimension 4]
      ,[Add_ Dimension 5] = Source.[Add_ Dimension 5]
      ,[KPI Value Decimal 1] = Source.[KPI Value Decimal 1]
      ,[KPI Value Decimal 2] = Source.[KPI Value Decimal 2]
      ,[KPI Value Decimal 3] = Source.[KPI Value Decimal 3]
      ,[KPI Value Decimal 4] = Source.[KPI Value Decimal 4]
      ,[KPI Value Decimal 5] = Source.[KPI Value Decimal 5]
      ,[KPI Value Boolean 1] = Source.[KPI Value Boolean 1]
      ,[KPI Value Boolean 2] = Source.[KPI Value Boolean 2]
      ,[KPI Value Boolean 3] = Source.[KPI Value Boolean 3]
      ,[KPI Value Date 1] = Source.[KPI Value Date 1]
      ,[KPI Value Date 2] = Source.[KPI Value Date 2]
      ,[KPI Value Time 1] = Source.[KPI Value Time 1]
      ,[KPI Value Time 2] = Source.[KPI Value Time 2]
      ,[KPI Value Duration 1] = Source.[KPI Value Duration 1]
      ,[KPI Value Duration 2] = Source.[KPI Value Duration 2]

	  	WHEN NOT MATCHED BY TARGET
	THEN INSERT
			([Company]
      ,[SK_KPICode]
      ,[SK_Location]
      ,[SK_POSTerminal]
      ,[SK_Staff]
      ,[SK_Vendor]
      ,[SK_Customer]
      ,[RowID]
	  ,[Signal Date]
	  ,[Signal Time]
      ,[Entry No_]
      ,[Deleted]
      ,[Add_ Dimension 1]
      ,[Add_ Dimension 2]
      ,[Add_ Dimension 3]
      ,[Add_ Dimension 4]
      ,[Add_ Dimension 5]
      ,[KPI Value Decimal 1]
      ,[KPI Value Decimal 2]
      ,[KPI Value Decimal 3]
      ,[KPI Value Decimal 4]
      ,[KPI Value Decimal 5]
      ,[KPI Value Boolean 1]
      ,[KPI Value Boolean 2]
      ,[KPI Value Boolean 3]
      ,[KPI Value Date 1]
      ,[KPI Value Date 2]
      ,[KPI Value Time 1]
      ,[KPI Value Time 2]
      ,[KPI Value Duration 1]
      ,[KPI Value Duration 2])
	   VALUES
		   (Source.[Company]
      ,Source.[SK_KPICode]
      ,Source.[SK_Location]
      ,Source.[SK_POSTerminal]
      ,Source.[SK_Staff]
      ,Source.[SK_Vendor]
      ,Source.[SK_Customer]
      ,Source.[RowID]
	  ,Source.[Signal Date]
	  ,Source.[Signal Time]
      ,Source.[Entry No_]
      ,Source.[Deleted]
      ,Source.[Add_ Dimension 1]
      ,Source.[Add_ Dimension 2]
      ,Source.[Add_ Dimension 3]
      ,Source.[Add_ Dimension 4]
      ,Source.[Add_ Dimension 5]
      ,Source.[KPI Value Decimal 1]
      ,Source.[KPI Value Decimal 2]
      ,Source.[KPI Value Decimal 3]
      ,Source.[KPI Value Decimal 4]
      ,Source.[KPI Value Decimal 5]
      ,Source.[KPI Value Boolean 1]
      ,Source.[KPI Value Boolean 2]
      ,Source.[KPI Value Boolean 3]
      ,Source.[KPI Value Date 1]
      ,Source.[KPI Value Date 2]
      ,Source.[KPI Value Time 1]
      ,Source.[KPI Value Time 2]
      ,Source.[KPI Value Duration 1]
      ,Source.[KPI Value Duration 2])

;

ELSE
SELECT 'RowCount' = 0
END

SELECT
	'RowCount' = @@rowcount
	
	
	
GO
	
-- CREATE Execution pipelines for Purchase orders


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[factPurchaseInvoiceExec]') AND type in (N'P'))
DROP PROCEDURE [dbo].[factPurchaseInvoiceExec]
GO

CREATE PROCEDURE [dbo].[factPurchaseInvoiceExec] 
	@CurrentRowID bigint,
	@NewRowID bigint
AS 
BEGIN

--CHECK if All needed Purchase staging tables exist
IF (Select count(*) from sys.tables where SCHEMA_NAME(schema_id) LIKE 'dbo' 
AND name IN ('stg$Purch_ Cr_ Memo Hdr_'
,'stg$Purch_ Cr_ Memo Line'
,'stg$Purch_ Inv_ Header'
,'stg$Purch_ Inv_ Line'
,'stg$Purch_ Rcpt_ Header'
,'stg$Purch_ Rcpt_ Line'
,'stg$Purchase Header'
,'stg$Purchase Header Archive'
,'stg$Purchase Line'
,'stg$Purchase Line Archive'))
= 10
AND COL_LENGTH('[dbo].[stg$Purchase Line]', '$systemCreatedAt') IS NOT NULL
	BEGIN
		EXEC  [dbo].[factPurchaseInvoice]
		@CurrentRowID = @CurrentRowID,
		@NewRowID = @NewRowID
	END
ELSE
	BEGIN
		SELECT 'RowCount' = 0
	END
END
GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[factPurchaseCreditMemoExec]') AND type in (N'P'))
DROP PROCEDURE [dbo].[factPurchaseCreditMemoExec]
GO

CREATE PROCEDURE [dbo].[factPurchaseCreditMemoExec] 
	@CurrentRowID bigint,
	@NewRowID bigint
AS 
BEGIN

--CHECK if All needed Purchase staging tables exist
IF (Select count(*) from sys.tables where SCHEMA_NAME(schema_id) LIKE 'dbo' 
AND name IN ('stg$Purch_ Cr_ Memo Hdr_'
,'stg$Purch_ Cr_ Memo Line'
,'stg$Purch_ Inv_ Header'
,'stg$Purch_ Inv_ Line'
,'stg$Purch_ Rcpt_ Header'
,'stg$Purch_ Rcpt_ Line'
,'stg$Purchase Header'
,'stg$Purchase Header Archive'
,'stg$Purchase Line'
,'stg$Purchase Line Archive'))
= 10
AND COL_LENGTH('[dbo].[stg$Purchase Line]', '$systemCreatedAt') IS NOT NULL
	BEGIN
		EXEC  [dbo].[factPurchaseCreditMemo]
		@CurrentRowID = @CurrentRowID,
		@NewRowID = @NewRowID
	END
ELSE
	BEGIN
		SELECT 'RowCount' = 0
	END
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[factPurchaseOrdersExec]') AND type in (N'P'))
DROP PROCEDURE [dbo].[factPurchaseOrdersExec]
GO

CREATE PROCEDURE [dbo].[factPurchaseOrdersExec] 
	@CurrentRowID bigint,
	@NewRowID bigint
AS 
BEGIN

--CHECK if All needed Purchase staging tables exist
IF (Select count(*) from sys.tables where SCHEMA_NAME(schema_id) LIKE 'dbo' 
AND name IN ('stg$Purch_ Cr_ Memo Hdr_'
,'stg$Purch_ Cr_ Memo Line'
,'stg$Purch_ Inv_ Header'
,'stg$Purch_ Inv_ Line'
,'stg$Purch_ Rcpt_ Header'
,'stg$Purch_ Rcpt_ Line'
,'stg$Purchase Header'
,'stg$Purchase Header Archive'
,'stg$Purchase Line'
,'stg$Purchase Line Archive'))
= 10
AND COL_LENGTH('[dbo].[stg$Purchase Line]', '$systemCreatedAt') IS NOT NULL
	BEGIN
		EXEC  [dbo].[factPurchaseOrders]
		@CurrentRowID = @CurrentRowID,
		@NewRowID = @NewRowID
	END
ELSE
	BEGIN
		SELECT 'RowCount' = 0
	END
END
GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[factPurchaseReceiptsExec]') AND type in (N'P'))
DROP PROCEDURE [dbo].[factPurchaseReceiptsExec]
GO

CREATE PROCEDURE [dbo].[factPurchaseReceiptsExec] 
	@CurrentRowID bigint,
	@NewRowID bigint
AS 
BEGIN

--CHECK if All needed Purchase staging tables exist
IF (Select count(*) from sys.tables where SCHEMA_NAME(schema_id) LIKE 'dbo' 
AND name IN ('stg$Purch_ Cr_ Memo Hdr_'
,'stg$Purch_ Cr_ Memo Line'
,'stg$Purch_ Inv_ Header'
,'stg$Purch_ Inv_ Line'
,'stg$Purch_ Rcpt_ Header'
,'stg$Purch_ Rcpt_ Line'
,'stg$Purchase Header'
,'stg$Purchase Header Archive'
,'stg$Purchase Line'
,'stg$Purchase Line Archive'))
= 10
AND COL_LENGTH('[dbo].[stg$Purchase Line]', '$systemCreatedAt') IS NOT NULL
	BEGIN
		EXEC  [dbo].[factPurchaseReceipts]
		@CurrentRowID = @CurrentRowID,
		@NewRowID = @NewRowID
	END
ELSE
	BEGIN
		SELECT 'RowCount' = 0
	END
END
GO